/**
 * FileName: EmployeeDaoImpl.java
 * 员工Dao实现类
 *
 * @author  王威
 * @Date    2021/11/28
 * @version 1.00
 **/
package com.site.dao.impl;
import com.alibaba.druid.util.JdbcUtils;
import com.site.dao.EmployeeDao;
import com.site.pojo.Access;
import com.site.pojo.Employee;
import com.site.pojo.Role;
import com.site.utils.JDBCUtils;
import com.site.vo.UserControlVO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class EmployeeDaoImpl implements EmployeeDao {
    /*按照员工名字查询*/
    @Override
    public Employee selectByname(Employee employee) throws SQLException {
        Employee employee1 = null;
        Connection conn = JDBCUtils.getConnection();
        ResultSet rst = JDBCUtils.executeQuery(conn, "SELECT * FROM `employee` where ename = ?",employee.getEname());
        while (rst.next()){
            employee1 = new Employee();
            employee1.setEid(rst.getLong("eid"));
            employee1.setEpassword(rst.getString("epassword"));
            employee1.setEname(rst.getString("ename"));
            employee1.setSex(rst.getString("sex"));
            employee1.setNationality(rst.getString("nationality"));
            employee1.setPhone(rst.getString("phone"));
            employee1.setBirthday(rst.getTimestamp("birthday"));
            employee1.setAddress(rst.getString("address"));
            employee1.setEmployDate(rst.getTimestamp("employ_date"));
            employee1.setState(rst.getLong("state"));
        }
        JDBCUtils.close(rst,null,conn);
        return employee1;
    }

    /*根据id查询员工*/
    @Override
    public Employee selectById(Employee employee) throws SQLException {
        Employee employee1 = null;
        Connection conn = JDBCUtils.getConnection();
        ResultSet rst = JDBCUtils.executeQuery(conn, "SELECT * FROM `employee` where eid = ?",employee.getEid());
        while (rst.next()){
            employee1 = new Employee();
            employee1.setEid(rst.getLong("eid"));
            employee1.setEname(rst.getString("ename"));
            employee1.setSex(rst.getString("sex"));
            employee1.setNationality(rst.getString("nationality"));
            employee1.setPhone(rst.getString("phone"));
            employee1.setBirthday(rst.getTimestamp("birthday"));
            employee1.setAddress(rst.getString("address"));
            employee1.setEmployDate(rst.getTimestamp("employ_date"));
            employee1.setEpassword(rst.getString("epassword"));
            employee1.setState(rst.getLong("state"));
        }
        JDBCUtils.close(rst,null,conn);
        return employee1;
    }

    /*查询所有*/
    @Override
    public List<Employee> selectAll() throws SQLException {
        List<Employee> list = new ArrayList<>();
        Connection conn = JDBCUtils.getConnection();
        ResultSet rst = JDBCUtils.executeQuery(conn, "SELECT * FROM `employee`");
        while (rst.next()){
            Employee employee = new Employee();
            employee.setEid(rst.getLong("eid"));
            employee.setEname(rst.getString("ename"));
            employee.setSex(rst.getString("sex"));
            employee.setNationality(rst.getString("nationality"));
            employee.setPhone(rst.getString("phone"));
            employee.setBirthday(rst.getTimestamp("birthday"));
            employee.setAddress(rst.getString("address"));
            employee.setEmployDate(rst.getTimestamp("employ_date"));
            employee.setState(rst.getLong("state"));
            employee.setEpassword(rst.getString("epassword"));
            list.add(employee);
        }
        JDBCUtils.close(rst,null,conn);
        return list;
    }
    /*添加数据*/
    @Override
    public int addEmployee(Connection con, Employee employee) throws SQLException {
        String sql = "INSERT INTO employee(eid,ename,epassword,sex,nationality,phone,birthday,address,employ_date,state)values(?,?,?,?,?,?,?,?,?,?)";
        Object[] prmas = {(Long) JDBCUtils.selectId(),employee.getEname(),employee.getEpassword(),employee.getSex(),employee.getNationality(),employee.getPhone(),
                employee.getBirthday(),employee.getAddress(),employee.getEmployDate(),employee.getState()};
        return JDBCUtils.executeUpdate(con,sql,prmas);
    }
    /*删除数据*/
    @Override
    public int delEmplyee(Connection con, Employee employee) {
        String sql = "delete from employee  where eid  =?";
        return JDBCUtils.executeUpdate(con,sql,new Object[]{employee.getEid()});
    }
    /*修改数据*/
    @Override
    public int updateEmlyee(Connection con, Employee employee) {
        String sql = "update ` hurricane_house `.` employee ` set`  ` ename ` = ?, ` epassword ` = ?, ` sex ` = ?,` nationality ` = ?, ` phone ` = ?, ` birthday ` = ?, ` address ` = ?, ` employ_date ` = ?, ` state ` = ? where` eid ` = ?";
        Object[] prmas = {employee.getEname(),employee.getEpassword(),employee.getSex(),employee.getNationality(),employee.getPhone(),
                employee.getBirthday(),employee.getAddress(),employee.getEmployDate(),employee.getEid(),employee.getState()};
        return JDBCUtils.executeUpdate(con,sql,prmas);
    }

    @Override
    public Employee login(Employee employee) throws SQLException {
        Employee employee1 = null;
        Connection conn = JDBCUtils.getConnection();
        ResultSet rst = JDBCUtils.executeQuery(conn, "SELECT * FROM `employee` where ename = ? and epassword = ?",employee.getEname(),employee.getEpassword());
        while (rst.next()){
            employee1 = new Employee();
            employee1.setEid(rst.getLong("eid"));
            employee1.setEpassword(rst.getString("epassword"));
            employee1.setEname(rst.getString("ename"));
            employee1.setSex(rst.getString("sex"));
            employee1.setNationality(rst.getString("nationality"));
            employee1.setPhone(rst.getString("phone"));
            employee1.setBirthday(rst.getTimestamp("birthday"));
            employee1.setAddress(rst.getString("address"));
            employee1.setEmployDate(rst.getTimestamp("employ_date"));
            employee1.setState(rst.getLong("state"));
        }
        JDBCUtils.close(rst,null,conn);
        return employee1;
    }
    /*用户管理视图*/
    @Override
    public List<UserControlVO> selectUserVO() throws SQLException {
        List<UserControlVO> listVO = new ArrayList<>();
        Connection conn = JDBCUtils.getConnection();
        ResultSet rst = JDBCUtils.executeQuery(conn,
                "SELECT e.eid,\n" +
                "\te.ename,\n" +
                "\te.sex,\n" +
                "\tr.role_name,\n" +
                "\ta.root_name,\n" +
                "\te.state\n" +
                " FROM employee e,employee_role er,access a,`role` r,role_access ra WHERE\n" +
                " e.eid = er.eid AND er.rid = r.rid AND r.rid = ra.rid AND ra.aid = a.aid");
        while (rst.next()){
            UserControlVO userControlVO = new UserControlVO();
            userControlVO.setEid(rst.getLong("e.eid"));
            userControlVO.setEname(rst.getString("e.ename"));
            userControlVO.setSex(rst.getString("e.sex"));
            userControlVO.setState(rst.getLong("state"));
            Role role = new Role();
            role.setRoleName(rst.getString("r.role_name"));
            userControlVO.setRole(role);
            Access access = new Access();
            access.setRootName(rst.getString("a.root_name"));
            userControlVO.setAccess(access);
            listVO.add(userControlVO);
        }
        JDBCUtils.close(rst,null,conn);
        return listVO;
    }


}
